package com.dao.yxy;

import com.bean.yxy.*;
import com.service.yxyService.GetDataService;
import com.util.yxy.Dbutil;
import com.util.yxy.Page;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * @author 余襄元
 * @date 2023/2/27
 * @projectName 二期项目
 **/
@Repository
public class GetDataDao {
    @Resource
    private HttpSession session;
    private String sql ;

    /**1
     * 收费管理1
     * 获取小区列表
     * @return
     */
    public Object getListxiaoqu(){
        Connection con= Dbutil.getConnection();
        sql = "select * FROM village";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**1
     * 收费管理1
     * 获取收费项目明细数据
     * @return
     */
    public Object getListshoufeixiangmu(Integer newPage){
        String sqlsum = "SELECT s.s_id,s.S_Name,s.S_money,s.S_date,v.vname,s.S_bz,e.ename,s.S_czdate FROM standard s \n" +
                "join chargetype c  on s.c_id=c.c_id join   village v   on v.vid = c.vid join  emp e  on  s.eid = e.eid ORDER BY s.S_id ";

       String sql = "SELECT s.s_id,s.S_Name,s.S_money,s.S_date,v.vname,s.S_bz,e.ename,s.S_czdate FROM standard s \n" +
               "join chargetype c  on s.c_id=c.c_id join   village v   on v.vid = c.vid join  emp e  on  s.eid = e.eid ORDER BY s.S_id LIMIT ?,?";
//        Integer newPage,Integer onePageSum,String sqlsum,String sql

        return Page.getBynewPageBysum(newPage,4,sqlsum,sql);
    }

    /**1
     * 收费管理1
     * 删除操作
     * @return
     */
    public boolean delectjilu(String sid){
        boolean b = isweijiaofeizhangdan(sid);
        if(!b){
            return false;
        }

        if(!delectjilu1(sid)){
            return  false;
        }
        Connection con=Dbutil.getConnection();
        sql = "DELETE FROM  standard  where  S_id=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            int i = queryRunner.update(con,sql,sid);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }
    //删除前先判断该类型对应的小区是否有未缴费账单存在则无法删除
    public boolean isweijiaofeizhangdan(String sid){
        Connection con=Dbutil.getConnection();
        sql = "SELECT * FROM dateal where S_id=? and D_settle ='f'";
        QueryRunner queryRunner = new QueryRunner();
        try {
            List<Map<String,Object>> lm = queryRunner.query(con,sql,new MapListHandler(),sid);
            if(lm.size()==0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }
    //先删除主项目的类型
    public boolean delectjilu1(String sid){
        Connection con=Dbutil.getConnection();
        sql = "DELETE FROM  chargetype  where  C_id=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            int i = queryRunner.update(con,sql,sid);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }

    public Object delectjilus(Object[] obj){
        int ss = obj.length;
        int rows = 0;
        for(int i=0;i<ss;i++){
            String sid =(String)obj[i];
            boolean b = isweijiaofeizhangdan(sid);
            if(!b){
                continue;
            }
            if(!delectjilu1(sid)){
                return  false;
            }
            Connection con=Dbutil.getConnection();
            sql = "DELETE FROM  standard  where  S_id=?";
            QueryRunner queryRunner = new QueryRunner();
            try {
                int ia = queryRunner.update(con,sql,sid);
                if(ia==1){
                    rows++;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                colose(con);
            }
        }
        if(rows>=1){
            if(rows==ss){
                return 3;
            }
            return 1;
        }else {
            return 2;
        }
    }

    /**1
     * 查询符合条件的数据
     * @param xqid
     * @param name
     * @return
     */
    public Object selectmags(Integer id,String xqid,String name){
        if(xqid.equals("0")){
            xqid="%";
        }
        if(name==""){
            name="%";
        }
        String sqlsum="SELECT s.s_id,s.S_Name,s.S_money,s.S_date,v.vid,v.vname,s.S_bz,e.ename,s.S_czdate FROM standard s\n" +
                "join chargetype c  on s.c_id=c.c_id\n" +
                "join   village v   on v.vid = c.vid\n" +
                "join  emp e  on  s.eid = e.eid\n" +
                "where v.vid like ? and  s.S_Name like CONCAT('%',?,'%')";
        String sql="SELECT s.s_id,s.S_Name,s.S_money,s.S_date,v.vid,v.vname,s.S_bz,e.ename,s.S_czdate FROM standard s\n" +
                "join chargetype c  on s.c_id=c.c_id\n" +
                "join   village v   on v.vid = c.vid\n" +
                "join  emp e  on  s.eid = e.eid\n" +
                "where v.vid like ? and  s.S_Name like CONCAT('%',?,'%') ORDER BY s.s_id LIMIT ? ,?";
       // Integer newPage,Integer onePageSum,String sqlsum,String sql
      return   Page.getBynewPageBysum2(id,4,sqlsum,sql,xqid,name);

    }

    /**1
     * 添加数据
     * @param y
     * @return
     */
    public boolean add(Yxy1 y){
        Map<String,Object> map = (Map<String,Object>) session.getAttribute("user");
        Integer id = (Integer) map.get("eid");
        boolean bl = isxiangmucunzai(y);
        if(!bl){
            return false;
        }
        if(!add1(y)){
            return false;
        }
        Integer cid = getcid();
        System.out.println(cid);
        Connection con=Dbutil.getConnection();
        sql = "INSERT into standard value(?,?,?,?,?,?,now(),?)";
        QueryRunner queryRunner = new QueryRunner();
        Object[] o  = new Object[]{cid,cid,y.getName2(),y.getDanjia(),y.getZouqi(),id,y.getBz()};
        try {
            int i =queryRunner.update(con,sql,o);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }
    //0先检测主项目名字是否存在
    public boolean isxiangmucunzai(Yxy1 y){
        Connection con=Dbutil.getConnection();
        sql="select * FROM chargetype where Cname=? and Vid=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
          List<Map<String,Object>> lm =  queryRunner.query(con,sql,new MapListHandler(),y.getName(),y.getXq());
          if(lm.size()==0){
              return true;
          }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }
    //1类型加一种
    public boolean add1(Yxy1 y){
        Map<String,Object> map = (Map<String,Object>) session.getAttribute("user");
        Integer id = (Integer) map.get("eid");
        Connection con=Dbutil.getConnection();
        sql = "INSERT into chargetype value(null,?,?,?,?,now())";
        QueryRunner queryRunner = new QueryRunner();
        Object[] o  = new Object[]{y.getName(),y.getBz(),y.getXq(),id};
        try {
            int i =queryRunner.update(con,sql,o);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }
    //2获取刚才加入类型的id
    public Integer getcid(){
        Connection con=Dbutil.getConnection();
        sql = "SELECT C_id FROM chargetype ORDER BY c_id desc limit 1";
        QueryRunner queryRunner = new QueryRunner();
        try {
            Yxy0 y = queryRunner.query(con,sql,new BeanHandler<Yxy0>(Yxy0.class));
            return y.getC_id();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }


    public Yxy3 getids(Yxy4 y){
        Connection con=Dbutil.getConnection();
        sql = "select c.c_id, s.S_id ,h.vid,h.tid,h.hid ,  u.uid ,h.aid FROM household h \n" +
                "join houses hh  on h.hid = hh.hid\n" +
                "join  village v on v.vid =h.vid\n" +
                "join tenement t on t.tid =h.tid\n" +
                "join unit u on u.uid = hh.uid\n" +
                "join chargetype c on c.vid = h.vid\n" +
                "join standard s on c.c_id=s.c_id\n" +
                "where c.cname =? and s.s_Name=? and t.ttname =? and u.uname=? and hh.honame=?";
        Object[] o = new Object[]{ y.getNamec(),y.getNames(),y.getLoudong(),y.getDanyuan(),y.getFangjian()};
        QueryRunner queryRunner = new QueryRunner();
        try {
            Yxy3  yy = queryRunner.query(con,sql,new BeanHandler<Yxy3>(Yxy3.class),o);
            System.out.println(yy);
            System.out.println(y);
            yy.setZhouqi(y.getZhouqi());
            yy.setDj(y.getDj());
            yy.setQsyl(y.getQsyl());
            yy.setJzyl(y.getJzyl());
            yy.setFy(y.getFy());
            yy.setZdr(y.getZdr());
            yy.setBz(y.getBz());

            return yy;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }















    /**2
     * 加载下拉框
     * @return
     */
    public Object getselect101(){
        Connection con=Dbutil.getConnection();
        sql = "SELECT s_id,s_name FROM standard";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**2
     *加载总数居
     * @return
     */
    public Object selectAllbiao(){
        Connection con=Dbutil.getConnection();
        sql = "SELECT d.D_id,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum,d.D_settle ,d.D_billdate,d.D_bz,e.ename,ss.S_czdate FROM dateal d\n" +
                "join  standard ss    on d.s_id =ss.s_id\n" +
                "join household h  on d.aid=h.aid\n" +
                "join  houses s  on h.hid=s.hid\n" +
                "join   village v  on v.vid=h.vid\n" +
                "join unit u    on s.uid =u.uid\n" +
                "join tenement t  on h.tid=t.tid\n" +
                "join emp e on d.eid=e.eid";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**2
     * 点击查询
     * @return
     */
    public Object selecttiaojianchaxun(String tid,String text){
        if(tid.equals("0")){
            tid="%";
        }
        if(text==""){
            text="%";
        }
        Connection con=Dbutil.getConnection();
        sql = "SELECT d.D_id,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum,d.D_settle ,d.D_billdate,d.D_bz,e.ename,ss.S_czdate FROM dateal d\n" +
                "join  standard ss    on d.s_id =ss.s_id\n" +
                "join household h  on d.aid=h.aid\n" +
                "join  houses s  on h.hid=s.hid\n" +
                "join   village v  on v.vid=h.vid\n" +
                "join unit u    on s.uid =u.uid\n" +
                "join tenement t  on h.tid=t.tid\n" +
                "join emp e on d.eid=e.eid\n" +
                "join chargetype  c on ss.C_id=c.c_id\n" +
                "where d.s_id like ? and s.honame like  ?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),tid,text);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;

    }

    /**2
     * 删除数据单个
     * @param Did
     * @return
     */
    public boolean delectchid(String Did){
        Connection con=Dbutil.getConnection();
        sql = "DELETE FROM dateal WHERE D_id = ?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            int i = queryRunner.update(con,sql,Did);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }

    /**2
     * 删除数据多个
     * @param
     * @return
     */
    public Object delectchids(Object[] obj){
        int ss = obj.length;
        int rows = 0;
        for(int i=0;i<ss;i++){
            String Did =(String)obj[i];
            boolean b = isjiaofei(Did);
            if(b){
                //存在已缴费
                continue;
            }
            Connection con=Dbutil.getConnection();
            sql = "DELETE FROM dateal WHERE D_id = ?";
            QueryRunner queryRunner = new QueryRunner();
            try {
                int ia = queryRunner.update(con,sql,Did);
                if(ia==1){
                    rows++;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                colose(con);
            }
        }
        if(rows>=1){
            if(rows==ss){
                return 3;
            }
            return 1;
        }else {
            return 2;
        }
    }

    //加载isjiafei
    public boolean isjiaofei(String Did){
        Connection con=Dbutil.getConnection();
        sql = "SELECT * FROM dateal where D_id=? and D_settle ='g'";
        QueryRunner queryRunner = new QueryRunner();
        try {
          List<Map<String,Object>> lm =  queryRunner.query(con,sql,new MapListHandler(),Did);
          if(lm.size()==1){
              return true;
          }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }


    /**2
     * 加载小区下拉框
     * @return
     */
    public Object xiaoqulist1(){
        Connection con=Dbutil.getConnection();
        sql = "SELECT * FROM village";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }


    /**2
     * 小区-->楼宇
     */
    public Object getlouyulist(String id){
        Connection con=Dbutil.getConnection();
        sql = "select * FROM tenement where vid=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /***2
     * 楼宇-->单元
     * @param id
     * @return
     */
    public Object getdanyuanlist(String id){
        Connection con=Dbutil.getConnection();
        sql = "select * FROM unit where tid=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**2
     * 单元-->房号
     * @param id
     * @return
     */
    public Object getfanghaolist(String id){
        Connection con=Dbutil.getConnection();
        sql = "select * FROM houses where uid=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 修改数据
     * @param did
     * @param
     * @param
     * @return
     */
    @RequestMapping(value = "xiugaimingxibiaoone",method = RequestMethod.POST)
    @ResponseBody
    public Object xiugaimingxibiaoone(String did,String zl){
        Connection con =  Dbutil.getConnection();
        QueryRunner q = new QueryRunner();
        sql="UPDATE dateal set  D_sum=? where D_id=?";
        try {
          int i =  q.update(con,sql,zl,did);
          if(i==1){
              return true;
          }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }


    /**2
     * 添加数据
     * @param y
     * @return
     */
    public boolean addzhangdanmingxi(Yxy3 y){
        Map<String,Object> map = (Map<String,Object>) session.getAttribute("user");
        Integer id = (Integer) map.get("eid");
        int aid = getaid(y);
        if(aid==0){
            System.out.println("aid获取失败");
            return false;
        }
        Connection con =  Dbutil.getConnection();
        QueryRunner q = new QueryRunner();
        sql="INSERT into dateal value\n" +
                "(null,null,?,?,?,?,?,?,?,?,?,?)";
        Object[] o = new Object[]{
                y.getS_id(),aid,id,y.getJzyl()-y.getQsyl(),"f",y.getZdr(),0,0,
                (y.getJzyl()-y.getQsyl())*y.getDj(),
                y.getBz()
        };
        try {
            int i =  q.update(con,sql,o);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }
    //获取住户aid
    public Integer getaid(Yxy3 y){
        Connection con =  Dbutil.getConnection();
        QueryRunner q = new QueryRunner();
        sql="SELECT Aid FROM `household`where hid = ? and vid = ? and tid =?";
        Object[] o = new Object[]{
              y.getHid(),y.getVid(),y.getTid()
        };
        try {
            Yxy2 y2 =  q.query(con,sql,new BeanHandler<Yxy2>(Yxy2.class) ,o);
            return y2.getAid();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return 0;
    }

    /**2
     * 根据小区变换显示项目相关信息
     */
    public Object addxiangmulist1(String vid){
        Connection con = Dbutil.getConnection();
        sql = "SELECT * FROM chargetype c\n" +
                "join standard s on c.c_id=s.c_id\n" +
                "where vid=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return    queryRunner.query(con , sql ,new MapListHandler(),vid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;


    }

    /**3
     * 加载所有数据
     */
    public Object getallfeiyongcuijiao(){
        Connection con =  Dbutil.getConnection() ;
        sql="SELECT d.D_count, d.D_id, h.hname ,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum, CONCAT(ss.s_money*d.D_sum) as summoney,d.D_billdate,d.D_bz,e.ename,ss.S_czdate ,h.Htel FROM dateal d\n" +
                "join  standard ss    on d.s_id =ss.s_id\n" +
                "join household h  on d.aid=h.aid\n" +
                "join  houses s  on h.hid=s.hid\n" +
                "join   village v  on v.vid=h.vid\n" +
                "join unit u    on s.uid =u.uid\n" +
                "join tenement t  on h.tid=t.tid\n" +
                "join emp e on d.eid=e.eid\n" +
                "where d.D_settle='f' ORDER BY d.d_id";
        QueryRunner queryRunner = new QueryRunner();
        try {
            return   queryRunner.query(con,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**3
     * 点击发送短信
     * @param
     * @return
     */
    public boolean fasongduanxing(String [] str){
        // te =手机号--发送短信
        int i = 0;
        for(String tel :str){
            if(fasong(tel)){
                i++;
            }
        }

        if(i==str.length){
            if(add1()){
                return true;
            }else {
                return false;
            }
        }else {
            return false;
        }
    }
    public boolean fasong (String tel){
        //发送短信

//        try {
//            //取消自动提交
//            Mysql.getConnection().setAutoCommit(false);
//            //发生错误调用回滚方法
//            Mysql.getConnection().rollback();
//            Mysql.getConnection().commit();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        return true;
    }
    public boolean add1(){
        Connection con =  Dbutil.getConnection();
        sql= "UPDATE dateal set D_count= D_count+1\n" +
                "where D_settle='f'";
        QueryRunner queryRunner =new QueryRunner();
        try {
            int i = queryRunner.update(con,sql);
            if(i>=1){
                return  true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }






    /**4收银结算
     * 结算
     * @return
     */
    public Object jieshuan(String did){
        Connection con =  Dbutil.getConnection();
        sql="UPDATE dateal SET  D_settle = 'g' where D_id=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            int i =   queryRunner.update(con,sql,did);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }

    /**4
     * 查询
     * @param fangshi
     * @return
     */
    public Object chaxunfout(String fangshi,String text){
        if(fangshi.equals("1")){
            //房屋
            sql= fangwu();
        }else {
            //住户
            sql= zhuhu();
        }
        Connection con =  Dbutil.getConnection();
        QueryRunner queryRunner =new QueryRunner();
        try {
            return   queryRunner.query(con,sql,new MapListHandler(),text);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;

    }
    //查询房屋
    public String fangwu(){
        System.out.println("房屋");
        sql = "SELECT d.D_id, h.hname ,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum, CONCAT(ss.s_money*d.D_sum) as summoney,d.D_billdate,d.D_bz,e.ename,ss.S_czdate ,h.Htel FROM dateal d join  standard ss    on d.s_id =ss.s_id join household h  on d.aid=h.aid join  houses s  on h.hid=s.hid join   village v  on v.vid=h.vid join unit u    on s.uid =u.uid join tenement t  on h.tid=t.tid join \n" +
                "emp e on d.eid=e.eid where d.D_settle='f' and h.hname like concat('%',?,'%') ORDER BY d.d_id";
        return sql;
    }
    //查询住户
    public String zhuhu(){
        System.out.println("住户");
        sql="SELECT d.D_id, h.hname ,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum, CONCAT(ss.s_money*d.D_sum) as summoney,d.D_billdate,d.D_bz,e.ename,ss.S_czdate ,h.Htel FROM dateal d join  standard ss    on d.s_id =ss.s_id join household h  on d.aid=h.aid join  houses s  on h.hid=s.hid join   village v  on v.vid=h.vid join unit u    on s.uid =u.uid join tenement t  on h.tid=t.tid join \n" +
                "emp e on d.eid=e.eid where d.D_settle='f' and s.honame = ? ORDER BY d.d_id";
        return sql;
    }


    /**
     * 5管理元加载自己的缴费明细
     * @param setter
     * @return
     */
    public Object bianhuachaxu(String setter){
        Connection con = Dbutil.getConnection();
        sql="SELECT d.D_settle, d.D_id, h.hname ,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum, CONCAT(ss.s_money*d.D_sum) as summoney,d.D_billdate,d.D_bz,e.ename,ss.S_czdate ,h.Htel FROM dateal d join  standard ss    on d.s_id =ss.s_id join household h  on d.aid=h.aid join  houses s  on h.hid=s.hid join   village v  on v.vid=h.vid join unit u    on s.uid =u.uid join tenement t  on h.tid=t.tid join \n" +
                "emp e on d.eid=e.eid  where d.D_settle=?  ORDER BY d.d_id  ";
        QueryRunner queryRunner  = new QueryRunner();
        try {
            return    queryRunner.query(con,sql,new MapListHandler(),setter);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 5住户加载自己的缴费明细
     * @param setter
     * @return
     */
    public Object bianhuachaxu1(String setter,String aid){
        Connection con = Dbutil.getConnection();
        sql="SELECT d.aid,  d.D_settle, d.D_id, h.hname ,v.vname,t.ttname,u.Uname,s.honame ,ss.s_name, ss.S_money,d.D_sum, CONCAT(ss.s_money*d.D_sum) as summoney,d.D_billdate,d.D_bz,e.ename,ss.S_czdate ,h.Htel FROM dateal d \n" +
                "left join  standard ss    on d.s_id =ss.s_id \n" +
                "left join household h  on d.aid=h.aid \n" +
                "left join houses s  on h.hid=s.hid \n" +
                "left join   village v  on v.vid=h.vid \n" +
                "left join unit u    on s.uid =u.uid \n" +
                "left join tenement t  on h.tid=t.tid\n" +
                "left join emp e on d.eid=e.eid  where  d.Aid=? and d.D_settle=? ORDER BY d.d_id  ";
        QueryRunner queryRunner  = new QueryRunner();
        try {
            return    queryRunner.query(con,sql,new MapListHandler(),aid,setter);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }


    /**
     * 住户自己缴费
     * @param aid
     * @return
     */
    public Object zhuhujiaofei(String aid){
       Connection con = Dbutil.getConnection();
       sql="UPDATE dateal set D_settle = 'g' where aid=? and D_settle = 'f' ";
       QueryRunner queryRunner = new QueryRunner();
        try {
          int i =   queryRunner.update(con,sql,aid);
          if(i>=1){
             return true ;
          }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return  false;
    }





    /**
     * 关闭Dbutisi
     */
    public void colose(Connection con){
        try {
            DbUtils.close(con);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 问卷时间校对
     */
    public void timejiaodui(){
        Connection con = Dbutil.getConnection();
        sql = "SELECT * FROM vote";
        try {
            PreparedStatement p= con.prepareStatement(sql);
            ResultSet re = p.executeQuery();
            while (re.next()){
                Date start = re.getDate("vote_vstart");
                Date end = re.getDate("vote_vend");
                Integer wjid = re.getInt("vote_vid");
                long date = System.currentTimeMillis();
                if(date<start.getTime()&&date<end.getTime()){
                    //未开始
                    shezhizhuangtai(wjid,"未开始");
                }else if(date>=start.getTime()&&date<=end.getTime()){
                    //正在进行
                    shezhizhuangtai(wjid,"正在进行");
                }else if(date>end.getTime()&&date>start.getTime()){
                    //已结束
                    shezhizhuangtai(wjid,"已结束");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
    }
    public boolean  shezhizhuangtai(Integer id, String zt){
        Connection con = Dbutil.getConnection();
        sql="UPDATE vote set vote_vcondition =? where vote_vid =?";
        QueryRunner queryRunner = new QueryRunner();
        try {
           int i =  queryRunner.update(con,sql,zt,id);
           if(i==1){
             return true;
           }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }


    /**
     * 问卷调查-查询所数据
     * @return
     */
    public Object getAllwenjuan(){
       Connection con =  Dbutil.getConnection();
       sql="SELECT * FROM `vote`";
       QueryRunner queryRunner =new QueryRunner();
        try {
           return queryRunner.query(con,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 获取题目
     * @param vid
     * @return
     */
    public Object xiangxixinxi(String vid){
        Connection con =  Dbutil.getConnection();
        sql="\n" +
                "SELECT * FROM `vote` v\n" +
                "join  `subject`  s on v.vote_vid = s.vote_vid\n" +
                "where v.vote_vid=?";
        QueryRunner queryRunner =new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),vid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 获取填写答案
     * @param vid
     * @param sid
     * @return
     */
    public Object getdaan(String vid,String sid){
        Connection con =  Dbutil.getConnection();
        sql="SELECT * FROM `vote` v\n" +
                "                join  `subject`  s on v.vote_vid = s.vote_vid\n" +
                "                join   answer a on  s.Subject_sid = a.Subject_sid\n" +
                "\t\t\t\t\t\t\t\tjoin   household h on h.Aid= a.Aaid\n" +
                "                where v.vote_vid=? and s.Subject_sid=? and s.Subject_stype='t'";
        QueryRunner queryRunner =new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),vid,sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 获取选择答案
     * @param vid
     * @param sid
     * @return
     */
    public Object getdaan1(String vid,String sid){
        Connection con =  Dbutil.getConnection();
        sql="SELECT * FROM `vote` v\n" +
                "                join  `subject`  s on v.vote_vid = s.vote_vid\n" +
                "                join   banswer b on  s.Subject_sid = b.Subject_sid\n" +
                "\t\t\t\t\t\t\t\tjoin   household  a on b.Baid =a.Aid\n" +
                "                where  s.Subject_stype='x' and v.vote_vid=? and s.Subject_sid=?";
        QueryRunner queryRunner =new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),vid,sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 添加答案
     */
    public Object adddaan(List<Questionnaire> lis){
        Map<String,Object> map = (Map<String,Object>) session.getAttribute("user");
        Integer id = (Integer) map.get("aid");
        int ia =0;
        for(int i = 0 ;i<lis.size();i++){
        Connection con =  Dbutil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
            Questionnaire a=lis.get(i);
            if(a.getFs().equals("t")){
            sql = "INSERT into answer VALUE(null,?,?,now(),?)" ;

            }else {
            sql = "INSERT into banswer VALUE(null,?,?,now(),?)";

            }
            try {
              int ii =  queryRunner.update(con,sql,a.getDaan(),a.getSid(),id);
            if(ii==1){
                ia++;
            }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                colose(con);
            }

        }

        if(ia== lis.size()){
            return true;
        }else {
            return false;
        }

    }


    /**
     *根据题目类型加载数据
     * @param sid
     * @return
     */
    public Object gettimuleixing(String sid){
        Connection con =  Dbutil.getConnection();
        sql="SELECT * FROM `subject` WHERE Subject_sid =?";
        QueryRunner queryRunner =new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapHandler(),sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }


    /**
     * 添加题目 -->同一个Connection对象进行事务处理
     * @param timu
     * @return
     */
    public Object addtimu(String timu,String vid,String lx){
        Dbutil mysql = new Dbutil();
        Connection con =  mysql.getConnection2();
      try {
          con.setAutoCommit(false);
          String sql1="SELECT * FROM `subject` where Subject_scontent=? and vote_vid=?";
          QueryRunner queryRunne1  = new QueryRunner();
          List<Map<String,Object>> lm =  queryRunne1.query(con,sql1,new MapListHandler(),timu,vid);
          if(lm.size()==0){
              String sql2="INSERT into `subject`value(null,?,?,?,now(),null)";
              QueryRunner queryRunne2  = new QueryRunner();
              int i =  queryRunne2.update(con,sql2,timu,lx,vid);
              if(i==1){
                  con.commit();
                  return true;
              }
          }
        } catch (SQLException e) {
          try {
              con.rollback();
          } catch (SQLException ex) {
              ex.printStackTrace();
          }
       }finally {
          colose(con);
      }
      return  false;
    }

    /**
     * 自动搜索标题
     * @param biaotimc
     * @return
     */
    public Object chaxunbiaotiba(String biaotimc){
        if(biaotimc.equals("")){
            biaotimc="%";
        }
        Connection con =  Dbutil.getConnection();
        sql="SELECT * FROM `vote` where vote_vtitled like concat('%',?,'%')";
        QueryRunner queryRunner =new QueryRunner();
        try {
            return queryRunner.query(con,sql,new MapListHandler(),biaotimc);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 删除题目
     * @param sid
     * @return
     */
    public Object delecttimu(String sid){
        Dbutil mysql = new Dbutil();
        Connection con =  mysql.getConnection2();
        try {
            con.setAutoCommit(false);
            String sql1="SELECT * FROM `answer` where Subject_sid =?";
            QueryRunner queryRunne1  = new QueryRunner();
            List<Map<String,Object>> lm =  queryRunne1.query(con,sql1,new MapListHandler(),sid);

            Connection co =  mysql.getConnection2();
            String sql="SELECT * FROM `Banswer` where Subject_sid =?";
            QueryRunner queryRunne  = new QueryRunner();
            List<Map<String,Object>> l =  queryRunne.query(co,sql,new MapListHandler(),sid);

            if(lm.size()==0&&l.size()==0){
                String sql2="DELETE FROM  `subject` where Subject_sid =?";
                QueryRunner queryRunne2  = new QueryRunner();
                int i =  queryRunne2.update(con,sql2,sid);
                if(i==1){
                    con.commit();
                    return true;
                }
            }
        } catch (SQLException e) {
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            colose(con);
        }
        return  false;
    }

    /**
     * 删除填写答案
     * @param
     * @return
     */
    public Object delectdaan(String daid){
        Connection con =  Dbutil.getConnection();
        sql="DELETE FROM answer WHERE Answer_aid=?";
        QueryRunner queryRunner =new QueryRunner();
        try {
            int i= queryRunner.update(con,sql,daid);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }

    /**
     * 删除雪中答案
     * @param daid
     * @return
     */
    public Object delectdaan1(String daid){
        Connection con =  Dbutil.getConnection();
        sql="DELETE FROM Banswer WHERE BAnswer_aid=?";
        QueryRunner queryRunner =new QueryRunner();
        try {
            int i= queryRunner.update(con,sql,daid);
            if(i==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }



    /**
     * 添加标题并文件上传
     * @param y
     * @param filename
     * @return
     */
    public Integer uploadFile(Yxy0 y,String filename){
        if(!yanzhengbiaoti(y)){
            return  -1;
        }
      Connection con =  Dbutil.getConnection();
      sql="INSERT into vote VALUE(null,?,?,?,?,?,now(),?,?,null)";
      Object[] o =new Object[]{
            y.getTimu(),y.getMiaoshu(),y.getZhuangtai(),
            y.getKaishisj(),y.getJieshusj(),filename,y.getCaozuoriq(),
      };
      QueryRunner queryRunner= new QueryRunner();
        try {
         return queryRunner.update(con,sql,o);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return 0;
    }
    /**
     * 验证标题是否存在
     */
    public boolean yanzhengbiaoti(Yxy0 y){
        Connection con =  Dbutil.getConnection();
        sql="select * FROM vote where vote_vtitled=?";
        QueryRunner queryRunner= new QueryRunner();
        try {
          List<Map<String,Object>> lm=  queryRunner.query(con,sql,new MapListHandler(),y.getTimu());
            if(lm.size()==0){
                return  true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }



    /**
     * 批量删除标题
     * @param ar
     * @return
     */
    public Object delectbiaoti(String[] ar){
        int i= 0;
        Dbutil m = new Dbutil();
        Connection con =  m.getConnection();
        try {
            con.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for(String a : ar){
            QueryRunner queryRunner = new QueryRunner();
            sql="DELETE FROM vote where vote_vid =?";
            try {
               int row =  queryRunner.update(con,sql,a);
               if(row==1){
                   i++;
               }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(i==ar.length){
            try {
                con.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                colose(con);
            }
            return true;
        }else {
            try {
                con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                colose(con);
            }
            return false;
        }
    }

    /**
     * 加载poll题目
     * @param vid
     * @return
     */
    public Object  gettimeByvid(String vid){
        Connection con =  Dbutil.getConnection();
        sql="select * FROM vote v\n" +
                "join `subject` s   on v.vote_vid =s.vote_vid\n" +
                "WHERE v.vote_vid =? and s.Subject_stype='x'";
        QueryRunner queryRunner = new QueryRunner();
        try {
           return queryRunner.query(con,sql,new MapListHandler(),vid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 投票加载 根据住户id分别判断标题是否投过票
     * @return
     */
    public Object istoupiao(String vid){
       Map<String,Object> mm =  (Map<String,Object>)session.getAttribute("user");
       Integer aid =(Integer) mm.get("Aid");
        Connection con =  Dbutil.getConnection();
        sql="SELECT * FROM poll WHERE vote_vid =? and aid=?";
        QueryRunner queryRunner = new QueryRunner();
        try {
          List<Map<String,Object>> m =  queryRunner.query(con,sql,new MapListHandler(),vid,aid);
            if(m.size()>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return false;
    }

    /**
     * 投票
     * @param fig
     * @return
     */
    public Object yonghutoupiao(String fig,String tpid){
        Map<String,Object> mm =  (Map<String,Object>)session.getAttribute("user");
        Integer aid =(Integer) mm.get("Aid");
        Connection con = Dbutil.getConnection();
        sql="INSERT into poll value(null,?,now(),?,null,null,?)";
        QueryRunner queryRunner = new QueryRunner();
        try {
         int i =   queryRunner.update(con,sql,aid,tpid,fig);
         if(i==1){
             return true;
         }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
            return false;
    }



    /**
     * 查看投票详情
     * @param
     * @return
     */
    public Object gettabletoupiaolist(String sid){
        Connection con = Dbutil.getConnection();
        sql="SELECT * FROM `subject` s\n" +
                "join  banswer b on b.Subject_sid=s.Subject_sid \n" +
                "join  household h   on h.Aid=b.Baid\n" +
                "where s.Subject_sid =?\n";
        QueryRunner queryRunner= new QueryRunner();
        try {
         return  queryRunner.query(con,sql,new MapListHandler(),sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }

    /**
     * 获取投票详情poll
     * @param sid
     * @return
     */
    public Object gettoupiao(String sid){
        Connection con = Dbutil.getConnection();
        sql="SELECT BAnswer_boolean ,count(*) as sum FROM `subject` s\n" +
                "join  banswer b   on s.Subject_sid=b.Subject_sid\n" +
                "where s.Subject_sid=?\n" +
                "GROUP BY BAnswer_boolean";
        QueryRunner queryRunner= new QueryRunner();
        try {
            return  queryRunner.query(con,sql,new MapListHandler(),sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            colose(con);
        }
        return null;
    }


}
